package gameSite;

import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 * Servlet implementation class DashboardValues
 */
@WebServlet("/DashboardValues")
public class DashboardValues extends HttpServlet {
	private static final long serialVersionUID = 1L;
	Connection conn;
	Statement stmt = null;
	ResultSet rs = null;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public DashboardValues() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		setValues(request);
		request.getRequestDispatcher("dashboard.jsp").forward(request, response);
		
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
	}
	
	
	protected void setValues(HttpServletRequest request){
		try{
			//STEP 4: Execute a query
				String sql;
		      System.out.println("Creating statement...");
		      stmt = RemoteConnection.getConnection().createStatement();
		      sql = "SELECT count(UserID) as customers  FROM Users";
		      rs = stmt.executeQuery(sql);
		      
		      if(rs.next()){		        		  
		         //Display values
		         System.out.print("User: " + rs.getString("customers") + " exists");
		         request.setAttribute("customers", rs.getString("customers"));
		      }
		      
		      sql = "SELECT count(orderID) as orders  FROM Orders";
		      rs = stmt.executeQuery(sql);
		      
		      if(rs.next()){		        		  
		         //Display values
		         System.out.print("Total Orders: " + rs.getString("orders"));
		         request.setAttribute("orders", rs.getString("orders"));
		      }
		      
		      sql = "SELECT count(productID) as items  FROM Inventory";
		      rs = stmt.executeQuery(sql);
		      
		      if(rs.next()){		        		  
		         //Display values
		         System.out.print("Total items: " + rs.getString("items"));
		         request.setAttribute("items", rs.getString("items"));
		      }
		      
		      sql = "SELECT sum(Total) as total  FROM Orders";
		      rs = stmt.executeQuery(sql);
		      
		      if(rs.next()){		        		  
		         //Display values
		         System.out.print("Total items: " + rs.getString("total"));
		         request.setAttribute("sales", rs.getString("total"));
		      }
		      
		      sql = "SELECT count(productID) as outofstock  FROM Inventory WHERE numberOfProducts<=0";
		      rs = stmt.executeQuery(sql);
		      
		      if(rs.next()){		        		  
		         //Display values
		         System.out.print("outofstock: " + rs.getString("outofstock"));
		         request.setAttribute("outOfStock", rs.getString("outofstock"));
		      }
		     	      
		      rs.close();
		      stmt.close();
		      RemoteConnection.closeConnection();
		     
		}
		catch(SQLException se){
		      //Handle errors for JDBC
		      se.printStackTrace();
		   }
		
	}

}
